﻿Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbException
Imports System.Data.DataSet
Imports System.Configuration

Public Class Producto
    Public connDB As OleDb.OleDbConnection
    Dim cmd As OleDb.OleDbCommand
    Public DataSet1 As DataSet
    Private Sub Producto_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        listar_combo()
        listar()
    End Sub

    Public Sub conexion()
        Try
            Dim conString As String = ConfigurationManager.ConnectionStrings("conDB").ConnectionString
            connDB = New OleDb.OleDbConnection(conString)
            'MsgBox("conextado corextamente")
        Catch ea As Exception
            MsgBox(ea.Message)
            connDB.Close()
        End Try
    End Sub

    Public Sub listar()
        conexion()
        Dim MiDataSet As New DataSet
        Dim MiAdaptador As New OleDb.OleDbDataAdapter
        Try
            connDB.Open()
            Dim sql As String = "SELECT Id_producto, [tp.Descripcion] as [Descripcion], Producto, Costo_produccion, PVP, PVM, Tiempo_elaboracion, tp.Id_tipo FROM Productos p, Tipo_producto tp where tp.Id_tipo = p.Id_tipo"

            Dim comando As New OleDbCommand(sql, connDB)
            MiAdaptador.SelectCommand = comando
            MiAdaptador.Fill(MiDataSet)
            DataGridView1.DataSource = MiDataSet.Tables(0)

        Catch ex As Exception
            MessageBox.Show("error " & Err.Number.ToString & vbCrLf & ex.Message)
            connDB.Close()
        End Try
    End Sub

    Public Sub listar_combo()
        conexion()
        Dim MiDataSet As New DataSet
        Dim MiAdaptador As New OleDb.OleDbDataAdapter
        Try
            connDB.Open()
            Dim sql As String = "SELECT Id_tipo, [descripcion] FROM Tipo_producto "

            Dim comando As New OleDbCommand(sql, connDB)
            MiAdaptador.SelectCommand = comando
            MiAdaptador.Fill(MiDataSet)

            ComboBox1.DataSource = MiDataSet.Tables(0)
            ComboBox1.DisplayMember = MiDataSet.Tables(0).Columns(1).Caption.ToString()
            ComboBox1.ValueMember = MiDataSet.Tables(0).Columns(0).Caption.ToString()
        Catch ex As Exception
            MessageBox.Show("error " & Err.Number.ToString & vbCrLf & ex.Message)
            connDB.Close()
        End Try
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Id_tipo As Integer = ComboBox1.SelectedValue
        Try
            conexion()
            connDB.Open()
            cmd = New OleDb.OleDbCommand("INSERT INTO Productos(Producto, Costo_produccion, PVP, PVM, Tiempo_elaboracion, Id_tipo) values ('" & TextBox1.Text & "', " & TextBox2.Text & ", " & TextBox3.Text & ", " & TextBox4.Text & ", " & TextBox5.Text & ", " & Id_tipo & ")", connDB)
            cmd.ExecuteNonQuery()
            connDB.Close()
            MsgBox("Producto Registrado Exitosamente")
            listar()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim Id_tipo As Integer = ComboBox1.SelectedValue
        Try
            conexion()
            connDB.Open()
            cmd = New OleDb.OleDbCommand("UPDATE Productos SET Producto = '" & TextBox1.Text & "', Costo_produccion = " & TextBox2.Text & ", PVP = " & TextBox3.Text & ", PVM = " & TextBox4.Text & ", Tiempo_elaboracion = " & TextBox5.Text & ", Id_tipo = " & Id_tipo & " WHERE Id_producto = " & TextBox7.Text, connDB)
            cmd.ExecuteNonQuery()
            connDB.Close()
            MsgBox("La Actualización del Producto fue Exitosa")
            listar()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            conexion()
            connDB.Open()
            cmd = New OleDb.OleDbCommand("DELETE FROM Productos WHERE Id_producto = " & TextBox7.Text, connDB)
            cmd.ExecuteNonQuery()
            connDB.Close()
            MsgBox("El Producto fue Eliminado Exitosamente")
            listar()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub DataGridView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged
        TextBox1.Text = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(2).Value
        TextBox2.Text = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(3).Value
        TextBox3.Text = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(4).Value
        TextBox4.Text = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(5).Value
        TextBox5.Text = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(6).Value
        TextBox7.Text = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value
        ComboBox1.SelectedValue = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(7).Value
    End Sub
End Class
